import 'dart:async';
import 'dart:io';
import 'package:flutter/foundation.dart';

import 'package:sqflite/sqflite.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:path/path.dart';
import 'package:path_provider/path_provider.dart';
import '../models/models.dart';

/// 数据库服务类
/// 负责SQLite数据库的初始化、表创建和基本操作
class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  factory DatabaseService() => _instance;
  DatabaseService._internal();

  static Database? _database;
  static const String _databaseName = 'tech_music_player.db';
  static const int _databaseVersion = 5;

  /// 获取数据库实例
  Future<Database> get database async {
    _database ??= await _initDatabase();
    return _database!;
  }

  /// 初始化数据库
  Future<Database> _initDatabase() async {
    if (kIsWeb) {
      // Web平台使用内存数据库
      return await openDatabase(
        _databaseName,
        version: _databaseVersion,
        onCreate: _onCreate,
        onUpgrade: _onUpgrade,
      );
    }

    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, _databaseName);

    return await openDatabase(
      path,
      version: _databaseVersion,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  /// 创建数据库表
  Future<void> _onCreate(Database db, int version) async {
    await _createMusicTable(db);
    await _createPlaylistTable(db);
    await _createPlaylistItemTable(db);
    await _createPlayHistoryTable(db);
    await _createPlayStatisticsTable(db);
    await _createUserSettingsTable(db);
    await _createThemeConfigTable(db);
  }

  /// 数据库升级
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    // 根据版本进行数据库升级
    if (oldVersion < 2) {
      // 版本1到版本2的升级：重命名字段以匹配模型类
      await db.execute(
          'ALTER TABLE music RENAME COLUMN cover_art_path TO cover_art');
      await db
          .execute('ALTER TABLE music RENAME COLUMN date_added TO created_at');
      await db.execute(
          'ALTER TABLE music RENAME COLUMN date_modified TO updated_at');
      await db.execute(
          'ALTER TABLE playlist RENAME COLUMN cover_image_path TO cover_image');
    }
    if (oldVersion < 4) {
      // 版本3到版本4的升级：删除并重建 play_history 表以修复字段问题
      await db.execute('DROP TABLE IF EXISTS play_history');
      await _createPlayHistoryTable(db);
    }
    if (oldVersion < 5) {
      // 版本4到版本5的升级：删除并重建 play_statistics 表以修复列名问题
      await db.execute('DROP TABLE IF EXISTS play_statistics');
      await _createPlayStatisticsTable(db);
    }
  }

  /// 创建音乐表
  Future<void> _createMusicTable(Database db) async {
    await db.execute('''
      CREATE TABLE music (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        artist TEXT NOT NULL,
        album TEXT,
        file_path TEXT NOT NULL,
        duration INTEGER NOT NULL,
        file_size INTEGER NOT NULL,
        cover_art TEXT,
        genre TEXT,
        year INTEGER,
        track_number INTEGER,
        bit_rate INTEGER,
        sample_rate INTEGER,
        is_favorite INTEGER DEFAULT 0,
        play_count INTEGER DEFAULT 0,
        last_played INTEGER,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      )
    ''');

    // 创建索引
    await db.execute('CREATE INDEX idx_music_title ON music(title)');
    await db.execute('CREATE INDEX idx_music_artist ON music(artist)');
    await db.execute('CREATE INDEX idx_music_album ON music(album)');
    await db.execute('CREATE INDEX idx_music_favorite ON music(is_favorite)');
    await db.execute('CREATE INDEX idx_music_created_at ON music(created_at)');
  }

  /// 创建播放列表表
  Future<void> _createPlaylistTable(Database db) async {
    await db.execute('''
      CREATE TABLE playlist (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        description TEXT,
        cover_image TEXT,
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL,
        music_ids TEXT,
        is_default INTEGER DEFAULT 0
      )
    ''');

    await db.execute('CREATE INDEX idx_playlist_name ON playlist(name)');
    await db.execute(
        'CREATE INDEX idx_playlist_created_at ON playlist(created_at)');
  }

  /// 创建播放列表项表
  Future<void> _createPlaylistItemTable(Database db) async {
    await db.execute('''
      CREATE TABLE playlist_item (
        id TEXT PRIMARY KEY,
        playlist_id TEXT NOT NULL,
        music_id TEXT NOT NULL,
        sort_order INTEGER NOT NULL,
        added_at INTEGER NOT NULL,
        FOREIGN KEY (playlist_id) REFERENCES playlist (id) ON DELETE CASCADE,
        FOREIGN KEY (music_id) REFERENCES music (id) ON DELETE CASCADE
      )
    ''');

    await db.execute(
        'CREATE INDEX idx_playlist_item_playlist_id ON playlist_item(playlist_id)');
    await db.execute(
        'CREATE INDEX idx_playlist_item_music_id ON playlist_item(music_id)');
    await db.execute(
        'CREATE INDEX idx_playlist_item_sort_order ON playlist_item(sort_order)');
  }

  /// 创建播放历史表
  Future<void> _createPlayHistoryTable(Database db) async {
    await db.execute('''
      CREATE TABLE play_history (
        id TEXT PRIMARY KEY,
        music_id TEXT NOT NULL,
        played_at INTEGER NOT NULL,
        play_duration INTEGER NOT NULL,
        is_completed INTEGER DEFAULT 0,
        progress REAL DEFAULT 0.0,
        source TEXT,
        FOREIGN KEY (music_id) REFERENCES music (id) ON DELETE CASCADE
      )
    ''');

    await db.execute(
        'CREATE INDEX idx_play_history_music_id ON play_history(music_id)');
    await db.execute(
        'CREATE INDEX idx_play_history_played_at ON play_history(played_at)');
  }

  /// 创建播放统计表
  Future<void> _createPlayStatisticsTable(Database db) async {
    await db.execute('''
      CREATE TABLE play_statistics (
        id TEXT PRIMARY KEY,
        total_play_time INTEGER DEFAULT 0,
        total_play_count INTEGER DEFAULT 0,
        today_play_time INTEGER DEFAULT 0,
        today_play_count INTEGER DEFAULT 0,
        most_played_music_ids TEXT,
        recent_played_music_ids TEXT,
        updated_at INTEGER NOT NULL
      )
    ''');
  }

  /// 创建用户设置表
  Future<void> _createUserSettingsTable(Database db) async {
    await db.execute('''
      CREATE TABLE user_settings (
        id TEXT PRIMARY KEY,
        theme_mode TEXT NOT NULL,
        volume REAL DEFAULT 1.0,
        play_mode TEXT NOT NULL,
        audio_quality TEXT NOT NULL,
        show_lyrics INTEGER DEFAULT 1,
        auto_play INTEGER DEFAULT 1,
        shuffle_enabled INTEGER DEFAULT 0,
        repeat_enabled INTEGER DEFAULT 0,
        sleep_timer_minutes INTEGER DEFAULT 0,
        equalizer_enabled INTEGER DEFAULT 0,
        bass_boost REAL DEFAULT 0.0,
        virtualizer REAL DEFAULT 0.0,
        updated_at INTEGER NOT NULL
      )
    ''');
  }

  /// 创建主题配置表
  Future<void> _createThemeConfigTable(Database db) async {
    await db.execute('''
      CREATE TABLE theme_config (
        id TEXT PRIMARY KEY,
        theme_name TEXT NOT NULL,
        primary_color TEXT NOT NULL,
        accent_color TEXT NOT NULL,
        background_color TEXT NOT NULL,
        surface_color TEXT NOT NULL,
        is_dark INTEGER NOT NULL,
        created_at INTEGER NOT NULL
      )
    ''');
  }

  /// 插入音乐记录
  Future<int> insertMusic(Music music) async {
    final db = await database;
    return await db.insert(
      'music',
      music.toJson(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  /// 获取所有音乐
  Future<List<Music>> getAllMusic() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'music',
      orderBy: 'created_at DESC',
    );
    return List.generate(maps.length, (i) => Music.fromJson(maps[i]));
  }

  /// 根据ID获取音乐
  Future<Music?> getMusicById(String id) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'music',
      where: 'id = ?',
      whereArgs: [id],
    );
    if (maps.isNotEmpty) {
      return Music.fromJson(maps.first);
    }
    return null;
  }

  /// 搜索音乐
  Future<List<Music>> searchMusic(String query) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'music',
      where: 'title LIKE ? OR artist LIKE ? OR album LIKE ?',
      whereArgs: ['%$query%', '%$query%', '%$query%'],
      orderBy: 'title ASC',
    );
    return List.generate(maps.length, (i) => Music.fromJson(maps[i]));
  }

  /// 更新音乐
  Future<int> updateMusic(Music music) async {
    final db = await database;
    return await db.update(
      'music',
      music.toJson(),
      where: 'id = ?',
      whereArgs: [music.id],
    );
  }

  /// 删除音乐
  Future<int> deleteMusic(String id) async {
    final db = await database;
    return await db.delete(
      'music',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// 插入播放列表
  Future<int> insertPlaylist(Playlist playlist) async {
    final db = await database;
    return await db.insert(
      'playlist',
      playlist.toJson(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  /// 获取所有播放列表
  Future<List<Playlist>> getAllPlaylists() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'playlist',
      orderBy: 'created_at DESC',
    );
    return List.generate(maps.length, (i) => Playlist.fromJson(maps[i]));
  }

  /// 插入播放历史
  Future<int> insertPlayHistory(PlayHistory history) async {
    final db = await database;
    return await db.insert(
      'play_history',
      history.toJson(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  /// 获取播放历史
  Future<List<PlayHistory>> getPlayHistory({int limit = 50}) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'play_history',
      orderBy: 'played_at DESC',
      limit: limit,
    );
    return List.generate(maps.length, (i) => PlayHistory.fromJson(maps[i]));
  }

  /// 删除播放历史
  Future<int> deletePlayHistory(String condition) async {
    final db = await database;
    return await db.delete(
      'play_history',
      where: condition,
    );
  }

  /// 插入或更新用户设置
  Future<int> insertOrUpdateUserSettings(UserSettings settings) async {
    final db = await database;
    return await db.insert(
      'user_settings',
      settings.toJson(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  /// 获取用户设置
  Future<UserSettings?> getUserSettings() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'user_settings',
      limit: 1,
    );
    if (maps.isNotEmpty) {
      return UserSettings.fromJson(maps.first);
    }
    return null;
  }

  /// 插入主题配置
  Future<int> insertThemeConfig(ThemeConfig themeConfig) async {
    final db = await database;
    return await db.insert(
      'theme_config',
      themeConfig.toJson(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  /// 获取所有主题配置
  Future<List<ThemeConfig>> getAllThemeConfigs() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'theme_config',
      orderBy: 'created_at ASC',
    );
    return List.generate(maps.length, (i) => ThemeConfig.fromJson(maps[i]));
  }

  /// 插入或更新播放统计
  Future<int> insertOrUpdatePlayStatistics(PlayStatistics statistics) async {
    final db = await database;
    // 创建新的Map并正确转换List<String>为字符串
    final statisticsMap = {
      'id': statistics.id,
      'total_play_time': statistics.totalPlayTime,
      'total_play_count': statistics.totalPlayCount,
      'today_play_time': statistics.todayPlayTime,
      'today_play_count': statistics.todayPlayCount,
      'most_played_music_ids': statistics.mostPlayedMusicIds.join(','),
      'recent_played_music_ids': statistics.recentlyPlayedMusicIds.join(','),
      'updated_at': statistics.updatedAt.millisecondsSinceEpoch,
    };

    return await db.insert(
      'play_statistics',
      statisticsMap,
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  /// 更新播放统计
  Future<int> updatePlayStatistics(PlayStatistics statistics) async {
    return await insertOrUpdatePlayStatistics(statistics);
  }

  /// 获取播放统计
  Future<PlayStatistics?> getPlayStatistics() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'play_statistics',
      limit: 1,
    );
    if (maps.isNotEmpty) {
      final map = Map<String, dynamic>.from(maps.first);
      // 将逗号分隔的字符串转换回List<String>
      final mostPlayedIds = map['most_played_music_ids'] as String?;
      final recentPlayedIds = map['recent_played_music_ids'] as String?;

      map['most_played_music_ids'] = mostPlayedIds?.isNotEmpty == true
          ? mostPlayedIds!.split(',')
          : <String>[];
      map['recent_played_music_ids'] = recentPlayedIds?.isNotEmpty == true
          ? recentPlayedIds!.split(',')
          : <String>[];

      return PlayStatistics.fromJson(map);
    }
    return null;
  }

  /// 删除播放统计
  Future<int> deletePlayStatistics(String id) async {
    final db = await database;
    return await db.delete(
      'play_statistics',
      where: 'id = ?',
      whereArgs: [id],
    );
  }

  /// 清空播放统计
  Future<void> clearPlayStatistics() async {
    final db = await database;
    await db.delete('play_statistics');
  }

  /// 增加播放统计数据
  Future<void> incrementPlayStatistics({
    required int playTime,
    required String musicId,
    bool isToday = true,
  }) async {
    try {
      final currentStats = await getPlayStatistics();
      final now = DateTime.now();

      if (currentStats == null) {
        // 创建新的统计记录
        final newStats = PlayStatistics(
          id: 'main_statistics',
          totalPlayTime: playTime,
          totalPlayCount: 1,
          todayPlayTime: isToday ? playTime : 0,
          todayPlayCount: isToday ? 1 : 0,
          mostPlayedMusicIds: [musicId],
          recentlyPlayedMusicIds: [musicId],
          updatedAt: now,
        );
        await insertOrUpdatePlayStatistics(newStats);
      } else {
        // 更新现有统计记录
        final updatedMostPlayed =
            List<String>.from(currentStats.mostPlayedMusicIds);
        final updatedRecentPlayed =
            List<String>.from(currentStats.recentlyPlayedMusicIds);

        // 更新最近播放列表（保持最新的20首）
        updatedRecentPlayed.remove(musicId);
        updatedRecentPlayed.insert(0, musicId);
        if (updatedRecentPlayed.length > 20) {
          updatedRecentPlayed.removeRange(20, updatedRecentPlayed.length);
        }

        // 更新最常播放列表
        if (!updatedMostPlayed.contains(musicId)) {
          updatedMostPlayed.add(musicId);
        }

        final updatedStats = currentStats.copyWith(
          totalPlayTime: currentStats.totalPlayTime + playTime,
          totalPlayCount: currentStats.totalPlayCount + 1,
          todayPlayTime: isToday
              ? currentStats.todayPlayTime + playTime
              : currentStats.todayPlayTime,
          todayPlayCount: isToday
              ? currentStats.todayPlayCount + 1
              : currentStats.todayPlayCount,
          mostPlayedMusicIds: updatedMostPlayed,
          recentlyPlayedMusicIds: updatedRecentPlayed,
          updatedAt: now,
        );

        await insertOrUpdatePlayStatistics(updatedStats);
      }
    } catch (e) {
      debugPrint('增加播放统计失败: $e');
    }
  }

  /// 重置今日播放统计
  Future<void> resetTodayStatistics() async {
    try {
      final currentStats = await getPlayStatistics();
      if (currentStats != null) {
        final updatedStats = currentStats.copyWith(
          todayPlayTime: 0,
          todayPlayCount: 0,
          updatedAt: DateTime.now(),
        );
        await insertOrUpdatePlayStatistics(updatedStats);
      }
    } catch (e) {
      debugPrint('重置今日统计失败: $e');
    }
  }

  /// 清空表数据
  Future<void> clearTable(String tableName) async {
    final db = await database;
    await db.delete(tableName);
  }

  /// 清空所有数据
  Future<void> clearAllData() async {
    await clearTable('music');
    await clearTable('playlist');
    await clearTable('playlist_item');
    await clearTable('play_history');
    await clearTable('play_statistics');
    await clearTable('user_settings');
    await clearTable('theme_config');
  }

  /// 关闭数据库
  Future<void> close() async {
    final db = _database;
    if (db != null) {
      await db.close();
      _database = null;
    }
  }

  /// 删除数据库文件
  Future<void> deleteDatabase() async {
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, _databaseName);
    final file = File(path);
    if (await file.exists()) {
      await file.delete();
    }
    _database = null;
  }

  /// 获取数据库信息
  Future<Map<String, dynamic>> getDatabaseInfo() async {
    final db = await database;
    final version = await db.getVersion();
    final documentsDirectory = await getApplicationDocumentsDirectory();
    final path = join(documentsDirectory.path, _databaseName);
    final file = File(path);
    final size = await file.length();

    return {
      'name': _databaseName,
      'version': version,
      'path': path,
      'size': size,
    };
  }
}
